package fr.pud.server.database;

import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;

import fr.pud.client.model.Neighbors;

/**
 * The useful query to get information from the server
 * @author Simon Devineau, Pierre Reliquet
 */
public class Query {
    public static final boolean WRITE_IN_FILE = true;

    /**
     * @param point
     *            , an id OSM
     * @return its neighbors
     * @throws SQLException
     */
    public static Neighbors getNeighbors(long point) throws SQLException {
        return getParentsAndSons(getWaysContainingPoint(point), point);
    }

    /**
     * @param ways
     *            , all the ways which contain the id oSM point
     * @param point
     *            , a long which correspond to the id OSM
     * @return a Neighbors object which contains all the parents and the sons of
     *         point
     */
    public static Neighbors getParentsAndSons(String ways, long point) {
        HashSet<Long> sons = new HashSet<Long>();
        HashSet<Long> parents = new HashSet<Long>();
        while (ways.length() > 0) {
            ArrayList<Long> a = parseCSV2Long(
                    ways.substring(1, ways.indexOf('}')), ',');
            if (a.indexOf(point) == 0) {
                sons.add(a.get(a.indexOf(point) + 1));
            }
            else {
                if (a.indexOf(point) == a.size() - 1) {
                    parents.add(a.get(a.indexOf(point) - 1));
                }
                else {
                    sons.add(a.get(a.indexOf(point) + 1));
                    parents.add(a.get(a.indexOf(point) - 1));
                }
            }
            ways = ways.substring(ways.indexOf('}') + 1);
        }
        System.out.println("Sons : " + sons);
        System.out.println("Parents : " + parents);
        return new Neighbors(point, sons, parents);
    }

    /**
     * This function return a String with all the ways which contain the point
     * specified
     * @param point
     *            , an integer wich is the OSM id of a point
     * @return String, a string with all the ways
     * @throws SQLException
     */
    public static String getWaysContainingPoint(long point) throws SQLException {
        Connection conn = Interaction.getConnection();
        String ways = "";
        // Création d'un objet statement
        System.out.println("Getting all the ways containing the point.");
        Statement state = conn.createStatement();
        // L'objet ResultSet contient le resultat de la requete SQL
        System.out.println("Execution running ...");
        ResultSet result = state
                .executeQuery("select * from planet_osm_ways where ARRAY["
                        + point + "]<@nodes;");
        while (result.next()) {
            if (result.getString("nodes") != null) {
                ways += result.getString("nodes");
            }
        }
        result.close();
        state.close();
        conn.close();
        return ways;
    }

    /**
     * @param query
     *            , which is suppose to be execute in the DB
     * @return, the results in a ResultSet format which will be parsed after
     * @throws SQLException
     */
    public static ResultSet launchQuery(String query, boolean write,
            String fileName) throws SQLException {
        ResultSet result = null;
        try {
            Connection conn = Interaction.getConnection();
            // Création d'un objet statement
            System.out.println("Lancement requete en cours...");
            Statement state = conn.createStatement();
            // L'objet ResultSet contient le resultat de la requete SQL
            System.out.println("Execution de la requete en cours ...");
            result = state.executeQuery(query);
            if (write == WRITE_IN_FILE) {
                resultSet2File(fileName, result);
            }
            else {
                resultSet2StandardOut(result);
            }
            state.close();
            conn.close();
        }
        catch (Exception e) {
            System.out.println("An error occured with the query!");
            e.printStackTrace();
        }
        return result;
    }

    /**
     * @param monTexte
     *            , the string which is to short
     * @return a string normalized with 20 characters
     */
    public static String normalizeLength(String monTexte) {
        while (monTexte.length() < 20) {
            monTexte += " ";
        }
        return monTexte;
    }

    /**
     * Generic method to parse a csv line
     * @param csv
     *            , a line where datas are separated by separator
     * @param separator
     * @return an ArrayList<String> which contains all the datas
     */
    public static ArrayList<String> parseCSV(String csv, char separator) {
        ArrayList<String> toReturn = new ArrayList<String>();
        String word = "";
        for (int i = 0; i < csv.length(); i++) {
            if (csv.charAt(i) == separator || i == csv.length()) {
                toReturn.add(word);
                word = "";
            }
            else {
                word += csv.charAt(i);
                if (i == csv.length() - 1) {
                    toReturn.add(word);
                }
            }
        }
        return toReturn;
    }

    /**
     * A generic method to parse a csv line if the csv contains long
     * @param csv
     *            , a line where datas are separated by separator
     * @param separator
     * @return an ArrayList<Long> which contains all the datas
     */
    public static ArrayList<Long> parseCSV2Long(String csv, char separator) {
        ArrayList<Long> toReturn = new ArrayList<Long>();
        String word = "";
        for (int i = 0; i < csv.length(); i++) {
            if (csv.charAt(i) == separator) {
                try {
                    toReturn.add(Long.parseLong(word));
                }
                catch (Exception e) {
                    System.out
                            .println("An error occured, the CSV format was not used with number");
                }
                word = "";
            }
            else {
                word += csv.charAt(i);
                if (i == csv.length() - 1) {
                    try {
                        toReturn.add(Long.parseLong(word));
                    }
                    catch (Exception e) {
                        System.out
                                .println("An error occured, the CSV format was not used with number");
                    }
                }
            }
        }
        return toReturn;
    }

    /**
     * @param fileName
     *            , the name of the file which will be created
     * @param result
     *            , a ResultSet which has to be parsed
     * @throws SQLException
     * @see {@link ResultSet}
     */
    public static void resultSet2File(String fileName, ResultSet result)
            throws SQLException {
        ResultSetMetaData resultMeta = result.getMetaData();
        System.out.println("Creating file : " + fileName + " ...");
        try {
            FileWriter aRemplir = new FileWriter(fileName);
            try {
                // On affiche le nom des colonnes
                System.out.println("Writing file... ");
                for (int i = 1; i <= resultMeta.getColumnCount(); i++) {
                    String colonne;
                    colonne = resultMeta.getColumnName(i).toUpperCase();
                    colonne = normalizeLength(colonne);
                    aRemplir.write("\t" + colonne + "\t|");
                }
                aRemplir.write("\n");
                int nbLignesFichiers = 0;
                while (result.next()) {
                    for (int i = 1; i <= resultMeta.getColumnCount(); i++) {
                        if (result.getString(i) != null) {
                            aRemplir.write("\t"
                                    + normalizeLength(result.getString(i))
                                    + "\t|");
                        }
                        else {
                            aRemplir.write("\t" + normalizeLength("") + "\t|");
                        }
                    }
                    aRemplir.write("\n-----------------------------------------------------------------------------------------------------------------------------------------------\n");
                    nbLignesFichiers = nbLignesFichiers + 1;
                }
            }
            finally {
                aRemplir.close();
                System.out.println("Ecriture dans le fichier terminee");
            }
        }
        catch (IOException e) {
            System.out.println("An error occured while writing the file! ");
            e.printStackTrace();
        }
    }

    /**
     * @param fileName
     *            , the name of the file which will be created
     * @param result
     *            , a ResultSet which has to be parsed
     * @throws SQLException
     * @see {@link ResultSet}
     */
    public static void resultSet2StandardOut(ResultSet result)
            throws SQLException {
        ResultSetMetaData resultMeta = result.getMetaData();
        // On affiche le nom des colonnes
        for (int i = 1; i <= resultMeta.getColumnCount(); i++) {
            String colonne;
            colonne = resultMeta.getColumnName(i).toUpperCase();
            colonne = normalizeLength(colonne);
            System.out.print("\t" + colonne + "\t|");
        }
        System.out.print("\n");
        int nbLignesFichiers = 0;
        while (result.next()) {
            for (int i = 1; i <= resultMeta.getColumnCount(); i++) {
                if (result.getString(i) != null) {
                    System.out.print("\t"
                            + normalizeLength(result.getString(i)) + "\t|");
                }
                else {
                    System.out.print("\t" + normalizeLength("") + "\t|");
                    // contenuColonne =
                    // if(contenuColonne.equals(""))
                    // contenuColonne = " ";
                    // contenuColonne = normalizeLength(contenuColonne);
                    // System.out.print("\t" + contenuColonne + "\t|");
                }
            }
            System.out
                    .print("\n-----------------------------------------------------------------------------------------------------------------------------------------------\n");
            nbLignesFichiers = nbLignesFichiers + 1;
        }
    }
}
